﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class SupplierCustomerSQL
    {
        public static string InsertSupplierCustomerMapping()
        {
            return @"
                INSERT INTO i_supplier_customer_mapping
           ([co_type]
           ,[short_code]
           ,[short_name]
           ,[full_name]
           ,[full_address]
           ,[city]
           ,[country]
           ,[account_code]
           ,[active]
           ,[agr_supp_apar_id]
           ,[agr_supp_apar_name]           
           ,[agr_cust_apar_id]
           ,[agr_cust_apar_name]
           ,[agr_cust_rel_value]
           ,[created_date]
           ,[modified_date]
            ,[interco]
             ,[sup_account]
            ,[cus_account]
            ,[cost_centre_chartering]
            ,client
            ,[apar_gr_id]
            ,[cus_apar_gr_id]
            )
     VALUES
           (@co_type
           ,@short_code
           ,@short_name
           ,@full_name
           ,@full_address
           ,@city
           ,@country
           ,@account_code
           ,@active
           ,@agr_supp_apar_id
           ,@agr_supp_apar_name           
           ,@agr_cust_apar_id
           ,@agr_cust_apar_name
           ,@agr_cust_rel_value
           ,@created_date
           ,@modified_date
            ,@interco
            ,@sup_account
            ,@cus_account
            ,@cost_centre_chartering
            ,@client
            ,@apar_gr_id
            ,@cus_apar_gr_id
            )";
        }
        public static string UpdateSupplierCustomerMapping()
        {
            return @"
            UPDATE i_supplier_customer_mapping SET
            [co_type] = @co_type
           ,[short_code] = @short_code
           ,[short_name] = @short_name
           ,[full_name] = @full_name
           ,[full_address] = @full_address
           ,[city] = @city
           ,[country] = @country
           ,[account_code] = @account_code
           ,[active] = @active
           ,[agr_supp_apar_id] = @agr_supp_apar_id
           ,[agr_supp_apar_name] = @agr_supp_apar_name
           ,[agr_cust_apar_id] = @agr_cust_apar_id
           ,[agr_cust_apar_name] = @agr_cust_apar_name
           ,[agr_cust_rel_value] = @agr_cust_rel_value
           ,[modified_date] = @modified_date
            ,[interco] = @interco
            ,[sup_account] = @sup_account
            ,[cus_account] = @cus_account
            ,[cost_centre_chartering] = @cost_centre_chartering
            ,[client] = @client
            ,interco_apar_id=@interco_apar_id
            ,legal_entity_id=@legal_entity_id
            ,cust_client=@cust_client
            ,[apar_gr_id] = @apar_gr_id
            ,[cus_apar_gr_id]=@cus_apar_gr_id
             WHERE supp_cust_id = @supp_cust_id";
        }
        public static string UpdateSupplierMappingAgresso()
        {
            return @"
            UPDATE i_supplier_customer_mapping SET
           [agr_supp_apar_id] = @agr_supp_apar_id
           ,[agr_supp_apar_name] = @agr_supp_apar_name
           ,[modified_date] = @modified_date
           ,[client] = @client
            ,[apar_gr_id]=@apar_gr_id
           WHERE supp_cust_id = @supp_cust_id";
        }
        public static string UpdateCustomerMappingAgresso()
        {
            return @"
            UPDATE i_supplier_customer_mapping SET
           [agr_cust_apar_id] = @agr_cust_apar_id
           ,[agr_cust_apar_name] = @agr_cust_apar_name
           ,[modified_date] = @modified_date
           ,[cust_client] = @cust_client
            ,[cus_apar_gr_id]=@cus_apar_gr_id
           WHERE supp_cust_id = @supp_cust_id";
        }
        public static string GetSupplierCustomerMappingByName()
        {
            return @"select * from i_supplier_customer_mapping where short_name=@short_name ";
        }
        public static string GetAllSupplierCustomerMapping()
        {
            return @"select * from i_supplier_customer_mapping order by full_name";
        }
        public static string GetAllSupplierCustomerMapping(string p)
        {
            return @"select * from i_supplier_customer_mapping 
            where
            ([co_type]+[short_code]
           +[short_name]
           +[full_name]
           +[full_address]
           +[city]
           +[country]
           +[account_code]
           +[active]
			+[agr_cust_apar_id]
			+[agr_supp_apar_id]) like '%" + p + @"%'
            order by full_name";
        }
        public static string GetAllSoftmarSupplierCustomer()
        {
            return @"
                Select distinct vOrgCoTypeSrch.CoType co_type,
                vOrganisation.Code short_code,
                vOrganisation.Name short_name,
                vOrganisation.FullName full_name,
                vCODIRAddressSrch.Address full_address,
                vOrganisation.City city,
                vOrganisation.Country country,
                vOrganisation.SLCode account_code,
                --vOrgUpdated.EnteredBy Entered_By,
                --vOrgUpdated.EnteredAt Entered_Date,
                vOrganisation.Active active
                 FROM VORGANISATION JOIN VCODIRADDRESSSRCH ON VORGANISATION.ID = VCODIRADDRESSSRCH.ORGANISATION 
                 JOIN VORGCOTYPESRCH ON VORGANISATION.ID=VORGCOTYPESRCH.ORGANISATION 
                 LEFT OUTER JOIN VORGUPDATED ON VORGANISATION.ID = VORGUPDATED.ORGANISATION  
                 WHERE (vOrgCoTypeSrch.CoType = 'Owner') AND 
                 (vOrganisation.FullName is NOT NULL) AND 
                 (vOrganisation.Active = 1) 
                 ORDER BY vOrganisation.FullName";
        }
        public static string GetSoftmarSupplierCustomerByName()
        {
            return @"Select distinct vOrgCoTypeSrch.CoType co_type,
                    vOrganisation.Code short_code,
                    vOrganisation.Name short_name,
                    vOrganisation.FullName full_name,
                    vCODIRAddressSrch.Address full_address,
                    vOrganisation.City city,
                    vOrganisation.Country country,
                    vOrganisation.SLCode account_code,
                    --vOrgUpdated.EnteredBy Entered_By,
                    --vOrgUpdated.EnteredAt Entered_Date,
                    vOrganisation.Active active
                     FROM VORGANISATION JOIN VCODIRADDRESSSRCH ON VORGANISATION.ID = VCODIRADDRESSSRCH.ORGANISATION 
                     JOIN VORGCOTYPESRCH ON VORGANISATION.ID=VORGCOTYPESRCH.ORGANISATION 
                     LEFT OUTER JOIN VORGUPDATED ON VORGANISATION.ID = VORGUPDATED.ORGANISATION  
                     WHERE --(vOrgCoTypeSrch.CoType = 'Owner') AND 
                     (vOrganisation.FullName is NOT NULL) -- AND 
                     --(vOrganisation.Active = 1) 
                     --AND vOrganisation.FullName = @full_name
                     AND vOrganisation.Name = @full_name";
        }

        public static string GetAllSupplierCustomerMappingByID()
        {
            return @"select * from i_supplier_customer_mapping where supp_cust_id=@supp_cust_id";
        }

        public static string GetSupplierMappingByFullName()
        {
            return @"select * from i_supplier_customer_mapping where full_name=@full_name";
        }
    }
}
